package com.aplink.generic.database;

import java.util.ArrayList;
import java.util.Enumeration;
import java.util.Hashtable;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

import com.aplink.generic.GenericApplication;
import com.aplink.generic.cache.MasterConfigManager;
import com.aplink.generic.pojo.MasterConfig;
import com.aplink.generic.pojo.RecordInfo;
import com.aplink.generic.pojo.TableInfo;
import com.aplink.generic.pojo.UpdateInfoList;
import com.aplink.generic.pojo.UpdateInfoRecord;
import com.aplink.generic.sharepreference.GenericSharePreference;
import com.aplink.generic.util.LogUtil;
import com.aplink.generic.xmlparser.RecordInfoParser;
import com.aplink.generic.xmlparser.TableInfoParser;
import com.aplink.generic.xmlpullparser.UpgradeDatabaseXmlPullParser;

public class DatabaseManager extends SQLiteOpenHelper {
	private static DatabaseManager sInstance = null;

	public static DatabaseManager getInstance() {
		if (DatabaseManager.sInstance == null) {
			DatabaseManager.sInstance = new DatabaseManager(GenericApplication
			        .getInstance().getApplicationContext(), MasterConfigManager
			        .getInstance().get(
			                GenericSharePreference.getInstance()
			                        .getMasterConfigFileID()));
		}
		return DatabaseManager.sInstance;
	}

	private final MasterConfig mMasterConfig;
	private final int mTableDataId;
	private final int mTableStructureId;

	private ArrayList<RecordInfo> recordInfoList;

	private ArrayList<TableInfo> tableInfoList;

	public DatabaseManager(final Context context,
	        final MasterConfig masterConfig) {
		super(context, masterConfig.getDatabaseName(), null, masterConfig
		        .getDatabaseVersion());
		this.mTableDataId = masterConfig.getDemoDataID(context);
		this.mTableStructureId = masterConfig.getTableNameStructureID(context);
		this.mMasterConfig = masterConfig;
	}

	private void createTableDemoData(final SQLiteDatabase db) {
		if (this.mTableDataId != 0) {
			ArrayList<String> tableInits = this.mMasterConfig
			        .getWebserviceConfig().getTableInitLists();
			this.recordInfoList = RecordInfoParser
			        .parseRecordsTables(this.mTableDataId);
			final boolean isDemoData = this.mMasterConfig.isDemo();
			try {
				db.beginTransaction();
				for (int i = 0; i < this.recordInfoList.size(); i++) {
					final RecordInfo recordInfo = this.recordInfoList.get(i);
					boolean canInitTable = isDemoData;
					if (!isDemoData) {
						if (!tableInits.contains(recordInfo.getmTableName())) {
							canInitTable = true;
						}
					}
					if (canInitTable) {
						initiateTable(db, recordInfo);
					}
				}
				db.setTransactionSuccessful();
			} catch (final android.database.SQLException e) {
				throw e;
			} finally {
				db.endTransaction();
			}
		}
	}

	private void createTableStructure(final SQLiteDatabase db) {
		if (this.mTableStructureId != 0) {
			if (this.mMasterConfig.getTableNameStructure() != null) {
				this.tableInfoList = TableInfoParser
				        .parserTableStructure(this.mTableStructureId);
				db.beginTransaction();
				try {
					for (int i = 0; i < this.tableInfoList.size(); i++) {
						final TableInfo currentTable = this.tableInfoList
						        .get(i);
						initiateTableStructure(db, currentTable);
					}
					db.setTransactionSuccessful();
				} catch (final android.database.SQLException e) {
					throw e;
				} finally {
					db.endTransaction();
				}
			}
		}
	}

	private String getCreateQuery(String table, SQLiteDatabase db,
	        String dropColumn) {
		String listColumn = String.format("PRAGMA table_info('%s')", table);
		Cursor cursor = db.rawQuery(listColumn, null);
		StringBuilder stringBuilder = new StringBuilder();
		stringBuilder.append("CREATE TABLE " + table + "(");
		while (cursor.moveToNext()) {
			String columnValue = cursor
			        .getString(cursor.getColumnIndex("name"));
			if (columnValue.equalsIgnoreCase(dropColumn)) {
				continue;
			}
			stringBuilder.append(columnValue);
			stringBuilder.append(" ");
			stringBuilder
			        .append(cursor.getString(cursor.getColumnIndex("type")));
			stringBuilder.append(" ");
			columnValue = cursor.getString(cursor.getColumnIndex("pk"));
			if (columnValue.equalsIgnoreCase("1")) {
				columnValue = cursor
				        .getString(cursor.getColumnIndex("notnull"));
				stringBuilder.append(" PRIMARY KEY ");
				if (columnValue.equalsIgnoreCase("1")
				        && cursor.getString(cursor.getColumnIndex("type"))
				                .equalsIgnoreCase("Integer")) {
					stringBuilder.append(" AUTOINCREMENT NOT NULL ");
				}
			}

			columnValue = cursor.getString(cursor.getColumnIndex("dflt_value"));
			if (!TextUtils.isEmpty(columnValue)) {
				stringBuilder.append(" DEFAULT " + columnValue);
			}

			stringBuilder.append(",");
		}
		stringBuilder.deleteCharAt(stringBuilder.length() - 1);
		stringBuilder.append(");");
		return stringBuilder.toString();
	}

	private void initiateTable(final SQLiteDatabase db,
	        final RecordInfo recordInfo) {
		for (int recordIndex = 0; recordIndex < recordInfo.getmRecordList()
		        .size(); recordIndex++) {
			final ArrayList<Hashtable<String, String>> mRecords = recordInfo
			        .getmRecordList();
			Hashtable<String, String> record = new Hashtable<String, String>();
			record = mRecords.get(recordIndex);
			final ContentValues initialValues = new ContentValues();
			final Enumeration<String> keys = record.keys();
			String key, value;
			while (keys.hasMoreElements()) {
				key = keys.nextElement();
				value = record.get(key);
				initialValues.put(key, value);
			}
			db.insert(recordInfo.getmTableName(), null, initialValues);
		}
	}

	private void initiateTableStructure(final SQLiteDatabase db,
	        final TableInfo table) {
		db.execSQL(table.getCreateTableQuery());
		final String message = table.getTableName() + " was created";
		LogUtil.info(message, "initiateStructure", getClass());
	}

	@Override
	public void onCreate(final SQLiteDatabase db) {
		createTableStructure(db);
		createTableDemoData(db);
	}

	@Override
	public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		super.onDowngrade(db, oldVersion, newVersion);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		MasterConfig masterConfig = MasterConfigManager.getInstance().get(
		        GenericSharePreference.getInstance().getMasterConfigFileID());
		UpdateInfoList updateInfoList = UpgradeDatabaseXmlPullParser.parse(
		        masterConfig.getDatabaseUpdateInfo(), GenericApplication
		                .getInstance().getApplicationContext());
		db.beginTransaction();
		try {
			for (int i = 0; i < updateInfoList.getUpdateList().size(); i++) {
				int version = updateInfoList.getUpdateList().keyAt(i);
				if (oldVersion < version && version <= newVersion) {
					for (UpdateInfoRecord infoRecord : updateInfoList
					        .getUpdateList().get(version)) {
						if (infoRecord.isDropColumn()) {
							String createQuery = getCreateQuery(
							        infoRecord.getTableName(), db,
							        infoRecord.getColumn());
							db.execSQL(infoRecord.getRenameSql());
							db.execSQL(createQuery);
							db.execSQL(infoRecord.getInsertSql(createQuery));
							db.execSQL(infoRecord.getDropBakTableSql());
						} else if (infoRecord.isDropTable()) {
							db.execSQL(infoRecord.getDropTableSql());
						} else {
							db.execSQL(infoRecord.getUpdateSql());
						}
					}
				}
			}
			db.setTransactionSuccessful();
		} catch (final android.database.SQLException e) {
			throw e;
		} finally {
			db.endTransaction();
		}

	}

	public int tableDataId() {
		return this.mTableDataId;
	}

	public int tableStructureId() {
		return this.mTableStructureId;
	}
}
